const { SSL_OP_DONT_INSERT_EMPTY_FRAGMENTS } = require('constants');
var request= require('request');
var mssql = require('mssql');
const Influx = require('influx'); //导包
const { nextTick } = require('process');
const { time } = require('console');


class TUtils {
    constructor(){

    };
    config={
        src:{
            url:"http://10.201.35.30:8090/enviSensor/selectByCompanyName",
            token:"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJhdWQiOiJhZG1pbiJ9.izglxuahSQb5by40gXqT1IcVbhmZQsE8cBu4X8tZKM0"
        },
        iotDb:{
            db:{
                database: 'mydb',
                username: 'root',
                password: 'root',
                hosts: [{ host: '10.205.21.19' }]
            },
            writeUrl:"http://10.205.21.19:8086/write?db=mydb&u=root&p=root",
            queryUrl:"http://10.205.21.19:8086/query?db=mydb&u=root&p=root"
        },
        msDb:{            
            db:{
                user: 'sa',
                password: 'DaQi@2020',
                server: '127.0.0.1', 
                database: 'QDG_AEIMS',
                //port: 1433,
                pool: {
                    min: 0, max: 10,
                    idleTimeoutMillis: 3000
                }
            }
        }
    };
    cb = function( callBack,p0,p1,p2){
        var me = this;
        try{
            if( callBack && callBack!=null){
                callBack(p0,p1,p2);
            }
        }
        catch(er){
            console.log(er);
        }
    };
    queryMs = function(sql , callBack){
        var me = this;
        try{
            var config = me.config.msDb.db;
            var connection = new mssql.ConnectionPool(config, function (err) {
                if (err) {
                    console.log(err);
                    return;
                }
                var ps = new mssql.PreparedStatement(connection);
                ps.prepare(sql, function (err) {
                    if (err){
                        console.log(err);
                        callBack(err, null);
                        return;
                    } 
                    ps.execute('', function (err, result) {
                        if (err){
                            console.log(err);
                            callBack(err, null);
                            return;
                        }
        
                        ps.unprepare(function (err) {
                            connection.close();
                            connection = null;
                            if (err){
                                console.log(err);
                                callback(err,null);
                                return;
                            }
                            callBack(err, result);
                        });
                    });
                });
            });
            
        }
        catch(er){
            console.log(er);
        }
    };
    queryMsPro = function( extendPath, ps , callback){
        var me = this;
        var res ={
            err:null,
            result:null
        }
        try{
            var getSql= function(sqlPath , parems){

            }
            var sql = getSql(extendPath, ps);
            me.queryMs(sql, function(erDB , resDB){
                res.err = erDB;
                res.result = resDB;
                me.cb(callback , res);
            });
        }
        catch(er){
            console.log(er);
        }
    };
    queryDB = function(sql , onQuery){
        var me = this;
        try{
            var url = me.config.iotDb.queryUrl;
            //url += "&q="+sql;
            var res =[];
            request({
                url:url,
                method: "POST",
                form:{q:sql}
                //body:"q="+sql
            }, function(err, response, body){
                if( err && err!=null){
                    console.log(err);
                } 
                var srcData = JSON.parse(body);

                if( srcData && srcData.results && srcData.results.length>0 && srcData.results[0].series){

                    for( var i =0; i<srcData.results[0].series.length;i++){
                        var serie = srcData.results[0].series[i];
                        var info = serie.tags?serie.tags:{};
                        for( var r =0; r<serie.values.length; r++){
                            for( var c =0;c<serie.columns.length;c++){
                                var col = serie.columns[c];
                                info[col] = serie.values[r][c];
                            }
                            res.push(info);
                        }
                        /*
                        if (serie.tags){
                            for( var c =0;c<serie.columns.length;c++){
                                var col = serie.columns[c];
                                for( var v =0; v<serie.values.length; v++){
                                    info[col] = serie.values[v][c];
                                }
                            }
                            res.push(info);
                        }
                        else{
                            for( var r =0; r<serie.values.length; r++){
                                for( var c =0;c<serie.columns.length;c++){
                                    var col = serie.columns[c];
                                    info[col] = serie.values[r][c];
                                }
                                res.push(info);
                            }
    
                        }
                        */
                    }
                }

                
                if( onQuery && onQuery!=null){
                    onQuery(res);
                }
            })
        }
        catch(er){
            console.log(er);
        }
    }; 
    /*采集原始数据*/ 
    inputSrcData = function(  onInput){
        var me = this;
        var res =[];
        var dataItems ={}
        try{
            var readData = function(onRead ){
                var url = me.config.src.url;
                var token =me.config.src.token;
                var datas =[];
                request({
                    url:url ,
                    method: "GET",
                    json: true,
                    headers: {
                        "token": token,
                    }
                }, function(error, response, body){
                    try{
                        if( !error && response.statusCode == 200){
                            if( body.errcode==0){
                                body.data.array.forEach(item => { 
                                    var inputFlag =item.code2 && (item.code2!=null) && item.code2.length>2;
                                    if( inputFlag){
                                        var pm10={
                                            measurement: 'T_Real',
                                            //time: (new Date(item.pm10Time)).getTime() ,
                                            time: item.pm10Time,
                                            tags: {
                                            dk:'pm10',
                                            type: item.type.toString() ,
                                            ip: item.code1 ,
                                            code: item.code2 ,
                                            enId: item.enviSensorUId ,
                                            label: item.label.replace(/\ +/g,"") ,
                                            factory: item.factory
                                            },
                                            fields : {
                                                rtd: item.pm10Rtd
                                            }
                                        };
                                        datas.push(pm10);
                                        var pm25={
                                            measurement: 'T_Real',
                                            //time: (new Date(item.pm25Time)).getTime() ,
                                            time: item.pm25Time ,
                                            fields: {
                                                rtd: item.pm25Rtd
                                            },
                                            tags: {
                                            dk:'pm25',
                                            type: item.type ,
                                            ip: item.code1 ,
                                            code: item.code2 ,
                                            enId: item.enviSensorUId ,
                                            label: item.label.replace(/\ +/g,""),
                                            factory: item.factory
                                            }
                                        };
                                        datas.push(pm25);
                                        var tsp={
                                            measurement: 'T_Real',
                                            //time: (new Date(item.tspTime)).getTime() ,
                                            time: item.tspTime,
                                            fields: {
                                                rtd: item.tspRtd
                                            },
                                            tags: {
                                            dk:'tsp',
                                            type: item.type ,
                                            ip: item.code1 ,
                                            code: item.code2 ,
                                            enId: item.enviSensorUId ,
                                            label: item.label.replace(/\ +/g,""),
                                            factory: item.factory
                                            }
                                        }; 
                                    }
                                });  
                            } 
                        }
                        if( onRead){
                            onRead(datas);
                        }
                    }
                    catch(er){
                        console.log(er)
                    }
                });
                
            }
            var doWrite = function(datas,onWrite){
                //var client = new Influx.InfluxDB(me.config.iotDb.db);
                var index =0;
                var postData = function(){
                    var next = function(){
                        index++;
                        postData();
            
                    }
                    if(index< datas.length){
                        var item = datas[index];
                        var url = me.config.iotDb.writeUrl;
                        var body="T_"+item.tags.key;
                        var body="T_Real";
                        for(var  p in item.tags){
                            var val =  item.tags[p];
                            if( !val && val==null){
                                val ="--";
                            }
                            body+=","+p+"="+val.toString().replace(/\ +/g,"");
                        }
                        body += " rtd="+item.fields.rtd.toString();
                        //var t = (item.time+8*24*3600*1000)*1000000
                        var t = item.time.substr(0,19)+".nnnnnnnnnZ";
                        var t = ((new Date(item.time)).getTime()+8*3600*1000)*1000000;
                        body += " "+t.toString();
            
                        request({
                            url:url,
                            method: "POST",
                            body:body
            
                        }, function(err, response, bodyRes){
                            if( err && err!=null){
                                console.log('write data error.', index, err, bodyRes);
                            } 
                            //console.log(body);
                            if (response.statusCode==400){
                                console.log(bodyRes);

                            }
                            index ++;
                            postData();
            
                        })
             
                    }
                    else{
                        //client.pool = null;
                        //client = null;
                        if(onWrite && onWrite!=null){
                            onWrite();
                        }
                    }
                }
                postData(); 
            }
            readData(function(datas){
                doWrite(datas, function(){
                    if( onInput && onInput!=null){
                        onInput();
                    }
                })
            })
            
        }
        catch(er){
            console.log(er);
        }
    };
    getReals = function(  onQuery){
        var me = this;
        var res =[];
        var dataItems ={}
        try{
            var sql = "select * from T_Real  group by factory , label ,type,dk ,ip, code, enId  order by time desc limit 1 "
            me.queryDB(sql , function(rows){
                rows.forEach(function(row){
                    var info = null;
                    var code = row.code;
                    var dk = row.dk;
                    var tm = row.time;
                    var val = row.rtd;

                    if( dataItems[code] ){
                        info = dataItems[code];
                    }
                    else{
                        info ={
                            code:row.code,
                            ip:row.ip,
                            enId:row.enId,
                            factory:row.factory,
                            label:row.label,
                            type:row.type
                        };
                        dataItems[code] = info;
                        res.push(info);
                    }
                    info[dk] = val;
                    info[dk+"Time"] = tm;

                });
                onQuery(res, dataItems); 
            })
        }
        catch(er){
            console.log(er);
        }
    };
    getHis = function( code , start ,end ,  onQuery){
        var me = this;
        var res =[];
        var dataItems ={}
        try{
            var sql = "select * from T_Real  where code='"+code+"' and time>='"+start+"' and time<'"+end+"'  order by time desc limit 1 ";
            me.queryDB(sql , function(rows){
                rows.forEach(function(row){
                    var info = null;
                    var code = row.code;
                    var dk = row.dk;
                    var tm = row.time;
                    var val = row.rtd;

                    if( dataItems[code] ){
                        info = dataItems[code];
                    }
                    else{
                        info ={
                            code:row.code,
                            ip:row.ip,
                            enId:row.enId,
                            factory:row.factory,
                            label:row.label,
                            type:row.type
                        };
                        dataItems[code] = info;
                        res.push(info);
                    }
                    info[dk] = val;

                });
                onQuery(res, dataItems); 
            })
        }
        catch(er){
            console.log(er);
        }
    };
    getAvg1H = function( start , end ,  onQuery){
        var me = this;
        var res =[];
        var dataItems ={}
        try{
            var sql = "select  mean(rtd) as  rtd from T_Real where time>'{start}' and time<'{end}'  group by factory , label ,type,dk ,ip, code, enId , time(1h)   fill(none)  order by time ";
            sql = sql.replace('{start}', start).replace('{end}', end);
            me.queryDB(sql , function(rows){
                rows.forEach(function(row){
                    var info = null;
                    var code = row.code;
                    var dk = row.dk;
                    var tm = row.time;
                    var tmInt = parseInt(0.001*( new Date(tm)).getTime());
                    var val = row.rtd;
                    var dataKey= code+"_"+tm;

                    if( dataItems[dataKey] ){
                        info = dataItems[dataKey];
                    }
                    else{
                        info ={
                            intTime: tmInt,
                            dataKey:dataKey,
                            code:row.code,
                            time:tm,
                            ip:row.ip,
                            enId:row.enId,
                            factory:row.factory,
                            label:row.label,
                            type:row.type,
                            pm10:-1,
                            pm25:-1,
                            tsp:-1,
                            co:-1,
                            so2:-1,
                            no2:-1,
                            o3:-1,
                            temp: -1,
                            humidity: -1,
                            windDirection: -1,
                            windSpeed:-1
                        };
                        dataItems[dataKey] = info;
                        res.push(info);
                    }
                    info[dk] = val; 
                });
                onQuery(res, dataItems); 
            })
        }
        catch(er){
            console.log(er);
        }
    };
    writeStatic2DB = function( start , end ,  callback){
        var me = this;  
        try{
            var getLastTm= function(onGet){
                var lastTms={};
                var sql ="select F_Code , max(F_IntTime) as F_MaxTm from T_Avg1H group by F_Code ";
                me.queryMs(sql, function(erDb , resDB){
                    resDB.recordset.forEach(function(item){
                        lastTms[item.F_Code] = parseInt( item.F_MaxTm);
                    });
                    onGet(lastTms);
                });
            };
            var writeDatas = function( datas, onWrite){
                var sqls = [];
                datas.forEach(function(item){
                    try{
                        var sql = "insert into T_Avg1H(F_IntTime , F_Time , F_DataKey , F_Code , F_EnId , F_Factory , F_Label , F_Type , F_Ip , F_Pm10 , F_Pm25 , F_Tsp , F_Co  , F_So2  , F_No2  , F_O3  , F_Tp  , F_Hu  , F_Ws  , F_Wd ) values ( ";
                        sql += [
                            item.intTime.toString() ,
                            "'"+item.time+"'" ,
                            "'"+item.dataKey+"'" ,
                            "'"+item.code+"'" ,
                            "'"+item.enId+"'" ,
                            "'"+item.factory+"'" ,
                            "'"+item.label+"'" ,
                            "'"+item.type+"'" ,
                            "'"+item.ip+"'" ,
                            item.pm10.toString() ,
                            item.pm25.toString() ,
                            item.tsp.toString()  ,
                            item.co.toString()  ,
                            item.so2.toString()  ,
                            item.no2.toString()  ,
                            item.o3.toString()  ,
                            item.temp.toString()  ,
                            item.humidity.toString()  ,
                            item.windSpeed.toString()  ,
                            item.windDirection.toString()  
                        ].join(",")+")";
                        sqls.push(sql);
                        //console.log(sql);
                    }
                    catch(erW){
                        console.log(erW);
                    }
                });
                var index =0;
                var res =  {successCount:0, ers:[]};
                var postSql = function(){
                    if( index< sqls.length){
                        var sql = sqls[index];
                        me.queryMs(sql, function(er, dbRes){
                            if( er){
                                res.ers.push(sql);
                            }
                            else{
                                res.successCount++;
                            }
                            index++;
                            postSql();
                        })
                    }
                    else{
                        me.cb(onWrite, res);
                    }
                }
                postSql();
            };
            getLastTm(function(lastTms){
                me.getAvg1H( start, end , function(resDatas, resDict){
                    var datas = resDatas.filter(function(item){
                        var flag = true;
                        if( lastTms[item.code]){
                            var maxTm  = lastTms[item.code]; 
                            flag = maxTm  < item.intTime;
                        }
                        return flag;
                    });
                    writeDatas(datas, function(resWrite){                    
                        me.cb(callback, resWrite);
                    })
                })

            });
            
        }
        catch(er){
            console.log(er);
        }
    };
}

module.exports=TUtils;
/*
utils = new TUtils();

utils.inputSrcData(function(){
    console.log('input');
    utils.writeStatic2DB('2020-12-01','2020-12-24' , function(er,res){
        console.log(JSON.stringify(res.recordset));
    })
})
utils.getReals(  function(res){
    console.log(JSON.stringify(res));
})
*/
